#include "sqlite-sql-executor.h"
#include <algorithm>
#include <iostream>
#include <cctype>

sql_executor_cmd::sql_executor_cmd()
    : mp_sql_db( NULL )
{

}

sql_executor_cmd::~sql_executor_cmd()
{

}

bool sql_executor_cmd::get_db_name( std::string &str_db_path )
{
    str_db_path = mstr_db_path_name;
    return true;
}

bool sql_executor_cmd::open( const std::string &str_path_name, bool b_create )
{
    int i_ret = 0;
    if ( (access( str_path_name.c_str(), F_OK ) != 0) && !b_create ) {
        return false;
    }

    do{
        std::lock_guard<std::mutex> guard(mo_db_lock);
        if (mp_sql_db) {
            close();
        }

        i_ret = sqlite3_open( str_path_name.c_str(), &mp_sql_db);
        if (SQLITE_OK != i_ret) {
            HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sqlite3_open failed. Ret=%d.\n", i_ret );
            return false;
        }

        if (!mp_sql_db) {
            HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sqlite3_open failed. mp_sql_db is NULL.\n" );
            return false;
        }

        mstr_db_path_name = str_path_name;
        sqlite3_busy_timeout( mp_sql_db, 3000 );
    } while( 0 );

    executor_cmd( "PRAGMA cache_size = 8000", i_ret );
    executor_cmd( "PRAGMA synchronous = OFF", i_ret );
    executor_cmd( "PRAGMA temp_store = MEMORY", i_ret );

    return true;
}

bool sql_executor_cmd::close()
{
    std::lock_guard<std::mutex> guard(mo_db_lock);
    if( mp_sql_db ){
        int i_ret = sqlite3_close( mp_sql_db );
        mp_sql_db = NULL;
        if( SQLITE_OK != i_ret ){
            HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sqlite3_close failed. ret=%d\n", i_ret );
            return false;
        }
    }

    return true;
}

bool sql_executor_cmd::executor_cmd( const std::string &str_sql_statement, int &i_ret )
{
    i_ret = -1;

    std::lock_guard<std::mutex> guard(mo_db_lock);
    if( NULL == mp_sql_db ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::executor_cmd failed. mp_sql_db is null.\n" );
        return false;
    }

    if( str_sql_statement.empty() ){
        return true;
    }

    i_ret = sqlite3_exec( mp_sql_db, str_sql_statement.c_str(), NULL, NULL, NULL );
    if( SQLITE_OK != i_ret ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sqlite3_exec failed.ret=%d,sql=%s\n", i_ret, str_sql_statement.c_str() );
        return false;
    }

    return true;
}

bool sql_executor_cmd::executor_cmd( const std::string &str_sql_statement, int &i_ret, int &i_effect_row )
{
    i_ret = -1;

    std::lock_guard<std::mutex> guard(mo_db_lock);
    if( NULL == mp_sql_db ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::executor_cmd failed. mp_sql_db is null.\n" );
        return false;
    }

    if( str_sql_statement.empty() ){
        return true;
    }

    i_ret = sqlite3_exec( mp_sql_db, str_sql_statement.c_str(), NULL, NULL, NULL );
    if( SQLITE_OK != i_ret ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sqlite3_exec failed.ret=%d,sql=%s\n", i_ret, str_sql_statement.c_str() );
        return false;
    }

    i_effect_row = sqlite3_changes( mp_sql_db );

    return true;
}


bool sql_executor_cmd::executor_sql( std::string &str_sql, st_sql_record **p_sr, int &i_ret )
{
    int i_cols = 0;
    int i_type = 0;

    const char *p_next = NULL;
    sqlite3_stmt *pst = NULL;

    st_sql_record *p_sqlite_rs = new st_sql_record();
    if( NULL == p_sqlite_rs ){
        HK_LOG_INFO_EX(HK_MACHINE_LOG_SUFFIX_FILE,  "create st_sql_record failed.\n" );
        return false;
    }

    std::lock_guard<std::mutex> guard(mo_db_lock);
    i_ret = sqlite3_prepare( mp_sql_db, str_sql.c_str(), str_sql.length() * 2, &p_sqlite_rs->mp_sql_stmt, &p_next );
    if( SQLITE_OK != i_ret ){
        std::string str_err = std::string( sqlite3_errmsg( mp_sql_db ) );
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "error on sqlite3_prepare: %s, ret=%d.\n", str_err.c_str(), i_ret );

        delete p_sqlite_rs;
        p_sqlite_rs = NULL;
        return false;
    }

    if( !p_sqlite_rs->init() ){
        std::string str_err = std::string( sqlite3_errmsg( mp_sql_db ) );
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "error on sqlite3_prepare: %s, ret=%d.\n", str_err.c_str(), sqlite3_errcode( mp_sql_db ) );

        this->close_rs( p_sqlite_rs );

        return false;
    }

    *p_sr = p_sqlite_rs;
    return true;

    return true;
}

bool sql_executor_cmd::executor_sql( std::string &str_sql, std::vector<std::string> &colum_name_vec, std::list< std::vector<std::string> > &table_info )
{
    st_sql_record *p_rs = NULL;
    int i_result = 0;

    if( !executor_sql( str_sql, &p_rs, i_result ) ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "Gatter data executor_sql return %d, sql = %s.\n", i_result, str_sql.c_str() );

        if( p_rs != NULL ){
            delete p_rs;
            p_rs = NULL;
        }

        return false;
    }

    p_rs->get_fields( colum_name_vec );
    while( !p_rs->is_eof() ){
        std::vector<std::string> item_vec;
        std::vector<std::string>::iterator it = colum_name_vec.begin();
        for( ; it != colum_name_vec.end(); ++it ){
            std::string str_val;
            p_rs->get_field_value( *it, str_val );
            item_vec.push_back( str_val );
        }

        table_info.push_back( item_vec );
        p_rs->move_next();
    }

    if( !close_rs( p_rs ) ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "Gatter data close_rs failed.\n");
        return false;
    }

    return true;
}

bool sql_executor_cmd::close_rs( st_sql_record *p_sr )
{
    if( NULL == p_sr ){
        return false;
    }

    bool b_ret = p_sr->exit();
    delete p_sr;

    return b_ret;

    return true;
}

bool sql_executor_cmd::get_db_table_context_list( const st_sql_table_info &tbl_info, std::vector<std::string> &col_name_vec, std::list<std::vector<std::string> > &tbl_context )
{
    std::set<std::string> str_key_set; //column primary key
    col_name_vec.clear();

    std::list<sql_column_info>::const_iterator it_tmp = tbl_info.col_info_list.begin();
    for( ; it_tmp != tbl_info.col_info_list.end(); ++it_tmp ){
        if( it_tmp->mb_primary_key ){
            str_key_set.insert( it_tmp->mstr_name );
        }

        col_name_vec.push_back( it_tmp->mstr_name );
    }

    st_sql_record *pRS = NULL;
    int i_result = 0;
    std::string str_sql = "select ";

    //build query sql
    std::vector<std::string>::iterator it_col = col_name_vec.begin();
    while( it_col != col_name_vec.end() ){
        str_sql += "[" + *it_col + "]";
        ++it_col;
        if( it_col != col_name_vec.end() ){
            str_sql += ",";
        }
    }

    str_sql += " from " + tbl_info.str_table_name;

    if( !executor_sql( str_sql, &pRS, i_result ) ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "get_db_table_context_list executor_cmd Failed. ret=%d,sql=%s\n", i_result, str_sql.c_str() );
        delete pRS;
        pRS = NULL;
        return false;
    }

    std::set<std::string> key_val_set;
    while( !pRS->is_eof() ){
        std::vector<std::string> item_val;
        std::string str_key_val;

        bool b_empty = false;
        std::vector<std::string>::iterator it = col_name_vec.begin();
        for( ; it != col_name_vec.end(); ++it ){
            std::string str_val;
            pRS->get_field_value( *it, str_val );
            if( !str_key_set.empty() && (str_key_set.end() != str_key_set.find( *it )) ){
                //if primary key duplicate, ignore this record, database may broken.
                if( str_val.empty() ){
                    b_empty = true;
                    break;
                }

                str_key_val += str_val;
            }

            item_val.push_back( str_val );
        }
        if( !b_empty ){
            if( !str_key_val.empty() ){
                if( key_val_set.find( str_key_val ) != key_val_set.end() ){
                    break;
                }

                key_val_set.insert( str_key_val );
            }
        } else {
            break;
        }

        pRS->move_next();
    }

    if( !close_rs( pRS ) ){
        return false;
    }

    return true;
}

//bool sql_executor_cmd::get_db_view_info(std::list<st_sql_table_info> &view_list)
//{
//    view_list.clear();

//    std::list<std::string> view_name_list;
//    if( !get_view_name_list( view_name_list ) ){
//        HK_LOG_INFO_EX(HK_MACHINE_LOG_SUFFIX_FILE,  "get_view_name_list failed.\n" );
//        return false;
//    }

//    std::list<std::string>::iterator it = view_name_list.begin();
//    for( ; it != view_name_list.end(); it++ ){
//        std::string str_view_name = *it;
//        st_sql_table_info tbl_info;
//        tbl_info.str_table_name = str_view_name;

//        if( !get_col_info_by_view_name( str_view_name, tbl_info.col_info_list ) ){
//            HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "get_col_info_by_view_name failed.\n" );
//            return false;
//        }

//        view_list.push_back( tbl_info );
//    }

//    return true;
//}

bool sql_executor_cmd::get_db_table_info(std::list<st_sql_table_info> &tbl_list)
{
    tbl_list.clear();

    std::list<std::string> tbl_name_list;
    if( !get_table_name_list( tbl_name_list ) ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "get_table_name_list failed.\n" );
        return false;
    }

    std::list<std::string>::iterator it = tbl_name_list.begin();
    for( ; it != tbl_name_list.end(); it++ ){
        st_sql_table_info tbl_info;
        tbl_info.str_table_name = *it;
        if( !get_col_info_by_table_name( tbl_info.str_table_name, tbl_info.col_info_list ) ){
            tbl_list.clear();
            return false;
        }

        tbl_list.push_back( tbl_info );
    }

    return true;
}

//bool sql_executor_cmd::get_view_name_list(std::list<std::string> &view_name_list)
//{
//    std::lock_guard<std::mutex> guard(mo_db_lock);

//    if( NULL == mp_sql_db ){
//        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_table_name_list failed.\n" );
//        return false;
//    }

//    std::string str_sql = "select * from sqlite_master where type =\"view\" ";

//    sqlite3_stmt *stat = NULL;
//    int i_ret = sqlite3_prepare( mp_sql_db, str_sql.c_str(), -1, &stat, 0 );
//    if( i_ret != 0 ){
//        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_table_name_list sqlite3_prepare Failed. ret = %d.\n", i_ret );
//        return false;
//    }

//    while( SQLITE_ROW == sqlite3_step( stat ) ){
//        std::string str_table_name = convert_to_string( sqlite3_column_text( stat, 2 ) );
//        if( str_table_name.empty() || (str_table_name == "") ){
//            continue;
//        }

//        view_name_list.push_back( str_table_name );
//    }

//    sqlite3_finalize( stat );

//    return true;
//}

//bool sql_executor_cmd::get_col_info_by_view_name(const std::string &str_view_name, std::list<sql_column_info> &col_list)
//{
//    if (str_view_name.empty()) {
//        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_col_info_by_view_name Filed, ViewerName is NULL\n" );
//        return false;
//    }

//    std::lock_guard<std::mutex> guard(mo_db_lock);
//    if (!mp_sql_db) {
//        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_col_info_by_view_name Filed, m_pDB is NULL\n" );
//        return false;
//    }

//    std::string str_sql = "select * from " + str_view_name;
//    int i_ret = 0;

//    sqlite3_stmt *stat = NULL;

//    i_ret = sqlite3_prepare( mp_sql_db, str_sql.c_str(), -1, &stat, 0);

//    if (SQLITE_OK != i_ret) {
//        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "GetViewColumnInfo Filed, sqlite3_prepare Failed nRet=%d, TableName=%s.\n", i_ret, str_view_name.c_str() );
//        return false;
//    }

//    int nColCount = sqlite3_column_count( stat );

//    for (int nIndex = 0; nIndex < nColCount; ++nIndex) {
//        sql_column_info ColInfo;
//        ColInfo.mstr_name = sqlite3_column_name(stat, nIndex);

//        col_list.push_back( ColInfo );
//    }

//    sqlite3_finalize(stat);

//    return true;
//}


bool sql_executor_cmd::get_table_name_list(std::list<std::string> &tbl_name_list)
{
    std::lock_guard<std::mutex> guard(mo_db_lock);

    if( NULL == mp_sql_db ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_table_name_list failed.\n" );
        return false;
    }

    std::string str_sql = "select * from sqlite_master where type =\"table\" ";

    sqlite3_stmt *stat = NULL;
    int i_ret = sqlite3_prepare( mp_sql_db, str_sql.c_str(), -1, &stat, 0 );
    if( i_ret != 0 ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_table_name_list sqlite3_prepare Failed. ret = %d.\n", i_ret );
        return false;
    }

    while( SQLITE_ROW == sqlite3_step( stat ) ){
        std::string str_table_name = convert_to_string( sqlite3_column_text( stat, 2 ) );
        if( str_table_name.empty() || (str_table_name == "") ){
            continue;
        }

        tbl_name_list.push_back( str_table_name );
    }

    sqlite3_finalize( stat );

    return true;
}

bool sql_executor_cmd::get_col_info_by_table_name(const std::string &str_table_name, std::list<sql_column_info> &col_list)
{
    std::lock_guard<std::mutex> guard(mo_db_lock);
    if( NULL == mp_sql_db ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_col_info_by_table_name failed.\n" );
        return false;
    }

    std::string str_sql = "select * from " + str_table_name;

    sqlite3_stmt *stat = NULL;
    int i_ret = sqlite3_prepare( mp_sql_db, str_sql.c_str(), -1, &stat, 0 );
    if( i_ret != 0 ){
        HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_col_info_by_table_name sqlite3_prepare Failed. ret = %d.\n", i_ret );
        return false;
    }

    //get column name
    int i_col_count = sqlite3_column_count( stat );
    for( int i_index = 0; i_index < i_col_count; ++i_index ){
        sql_column_info st_colum_info;
        st_colum_info.mstr_name = sqlite3_column_name( stat, i_index );

//        char const *p_data_type = NULL;
//        char const *p_coll_seq = NULL;
//        int p_not_null = 0;
//        int p_primary_key = 0;
//        int p_auto_inc = 0;

//        if( SQLITE_OK != sqlite3_table_column_metadata( mp_sql_db, NULL, str_table_name.c_str(), st_colum_info.mstr_name.c_str(),
//                                                        &p_data_type, &p_coll_seq, &p_not_null, &p_primary_key, &p_auto_inc ) ){
//            HK_LOG_ERROR_EX(HK_MACHINE_LOG_SUFFIX_FILE, "sql_executor_cmd::get_col_info_by_table_name sqlite3_table_column_metadata failed. tbl_name=%s, column_index=%d",str_table_name.c_str(), i_index );
//            sqlite3_finalize( stat );
//            return false;
//        }

//        if( p_primary_key ){
//            st_colum_info.mb_primary_key = true;
//        }

//        std::string str_col_type = p_data_type;
//        std::transform( str_col_type.begin(), str_col_type.end(), str_col_type.begin(), ::toupper );

        if( i_index == 0 ){
            st_colum_info.mb_primary_key = true;
        } else {
            st_colum_info.mb_primary_key = false;
        }

        int i_type = sqlite3_column_type( stat, i_index );
        if( i_type == SQLITE_INTEGER ){
            st_colum_info.me_type = SQL_DATA_TYPE_INT;
        } else if( i_type == SQLITE_TEXT ){
            st_colum_info.me_type = SQL_DATA_TYPE_STRING;
            st_colum_info.mui_len = 255;
        }

        col_list.push_back( st_colum_info );
    }

    return true;

}
